package reports

import (
	"fmt"
	"time"

	"shqsoft.cn/pas3ui/base"
)

func createTable(tName string, createSql string) (ok bool, err error) {
	sqlStr := fmt.Sprintf("select count(*) from system.tables c where database ='%s' and name = ?", base.AppConf.CHDBName)
	var count int
	err = base.CHDB.Raw(sqlStr, tName).Row().Scan(&count)
	if err != nil {
		return
	}
	if count > 0 {
		return
	}
	ok = true
	err = base.CHDB.Exec(createSql).Error
	if err != nil {
		return
	}
	sqlStr = fmt.Sprintf("TRUNCATE TABLE %s", tName)
	err = base.CHDB.Exec(sqlStr).Error
	return
}

func createCompBase(date *time.Time) (err error) {
	tName := fmt.Sprintf("TMP_COMP_BASE_%s", date.Format("20060102"))
	createSql := fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s
	(ACID UInt64,JLID UInt64,CUID UInt64,UTID UInt32,ITID UInt32,PERD UInt16,CYNO FixedString(2),
	WJFL UInt8,TPNO UInt16,PPNO UInt16,FPNO UInt8,EMID UInt32,ACBL Float64,AVVL Float64)
	ENGINE=Memory`, tName)
	if ok, err := createTable(tName, createSql); err != nil || !ok {
		return err
	}
	sqlStr := fmt.Sprintf(`INSERT INTO %s
	WITH toDate(?) AS YEDATE,toStartOfYear(YEDATE) AS YSDATE,
		if(SDATE<YSDATE,YSDATE,SDATE) AS SDATE2,if(EDATE>YEDATE,YEDATE,EDATE) AS EDATE2,
		dateDiff('day',YSDATE,YEDATE)+1 AS DAYS1,dateDiff('day',SDATE2,EDATE2)+1 AS DAYS2,
		dictGetUInt32('DICT_USER_PUBLIC','EMID',toUInt64(UTID)) AS PEMID
	select ACID,JLID,CUID,UTID,ITID,PERD,CYNO,WJFL,TPNO,PPNO,FPNO,if(EMID2=0,PEMID,EMID2) EMID,
		if(YEDATE>=SDATE2 AND YEDATE<=EDATE2,ACBL*DSVL,0) ACBL,
		AVVL*DAYS2/DAYS1*DSVL AVVL
	from T_COMP_ACCO_BASE a left join (
		select SDATE,EDATE,ACID,DIVI.EMID EMID2,DIVI.DSVL DSVL
		from T_COMP_ACCO_DIVI_ZIPPER ARRAY JOIN DIVI
		where SDATE<=YEDATE AND EDATE>=YSDATE
		) b on a.ACID=b.ACID
	where a.BSNF=toYear(YEDATE)`, tName)

	err = base.CHDB.Exec(sqlStr, date.Format("2006-01-02")).Error
	return
}

func createIndiSaveBase(date *time.Time) (err error) {
	tName := fmt.Sprintf("TMP_INDI_SAVE_BASE_%s", date.Format("20060102"))
	createSql := fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s
	(ACID UInt64,JLID UInt64,CUID UInt64,UTID UInt32,ITID UInt32,PERD UInt16,CYNO FixedString(2),
	TPNO UInt16,EMID UInt32,ACBL Float64,AVVL Float64)
	ENGINE=Memory`, tName)
	if ok, err := createTable(tName, createSql); err != nil || !ok {
		return err
	}
	sqlStr := fmt.Sprintf(`INSERT INTO %s 
	WITH toDate(?) AS YEDATE,toStartOfYear(YEDATE) AS YSDATE,
		if(SDATE<YSDATE,YSDATE,SDATE) AS SDATE2,if(EDATE>YEDATE,YEDATE,EDATE) AS EDATE2,
		dateDiff('day',YSDATE,YEDATE)+1 AS DAYS1,dateDiff('day',SDATE2,EDATE2)+1 AS DAYS2,
		dictGetUInt32('DICT_USER_PUBLIC','EMID',toUInt64(UTID)) AS PEMID
	select ACID,JLID,CUID,UTID,ITID,PERD,CYNO,TPNO,if(EMID2=0,PEMID,EMID2) EMID,
		if(YEDATE>=SDATE2 AND YEDATE<=EDATE2,ACBL*DSVL,0) ACBL,
		AVVL*DAYS2/DAYS1*DSVL AVVL
	from T_COMP_ACCO_BASE a left join (
		select SDATE,EDATE,ACID,DIVI.EMID EMID2,DIVI.DSVL DSVL 
		from T_COMP_ACCO_DIVI_ZIPPER ARRAY JOIN DIVI
		where SDATE<=YEDATE AND EDATE>=YSDATE
		) b on a.ACID=b.ACID
	where a.BSNF=toYear(YEDATE)`, tName)
	err = base.CHDB.Exec(sqlStr, date.Format("2006-01-02")).Error
	return
}

func createIndiLoanBase(date *time.Time) (err error) {
	tName := fmt.Sprintf("TMP_INDI_LOAN_BASE_%s", date.Format("20060102"))
	createSql := fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s
	(ACID UInt64,JLID UInt64,CUID UInt64,UTID UInt32,ITID UInt32,PERD UInt16,CYNO FixedString(2),
	WJFL UInt8,KDID UInt8,TPNO UInt16,EMID UInt32,ACBL Float64,AVVL Float64)
	ENGINE=Memory`, tName)
	if ok, err := createTable(tName, createSql); err != nil || !ok {
		return err
	}
	if ok, err := createTable(tName, createSql); err != nil || !ok {
		return err
	}
	sqlStr := fmt.Sprintf(`INSERT INTO %s 
	WITH toDate(?) AS YEDATE,toStartOfYear(YEDATE) AS YSDATE,
		if(SDATE<YSDATE,YSDATE,SDATE) AS SDATE2,if(EDATE>YEDATE,YEDATE,EDATE) AS EDATE2,
		dateDiff('day',YSDATE,YEDATE)+1 AS DAYS1,dateDiff('day',SDATE2,EDATE2)+1 AS DAYS2,
		dictGetUInt32('DICT_USER_PUBLIC','EMID',toUInt64(UTID)) AS PEMID
	select ACID,JLID,CUID,UTID,ITID,PERD,CYNO,WJFL,KDID,TPNO,if(EMID2=0,PEMID,EMID2) EMID,
		if(YEDATE>=SDATE2 AND YEDATE<=EDATE2,ACBL*DSVL,0) ACBL,
		AVVL*DAYS2/DAYS1*DSVL AVVL
	from T_COMP_ACCO_BASE a left join (
		select SDATE,EDATE,ACID,DIVI.EMID EMID2,DIVI.DSVL DSVL 
		from T_COMP_ACCO_DIVI_ZIPPER ARRAY JOIN DIVI
		where SDATE<=YEDATE AND EDATE>=YSDATE
		) b on a.ACID=b.ACID
	where a.BSNF=toYear(YEDATE)`, tName)
	err = base.CHDB.Exec(sqlStr, date.Format("2006-01-02")).Error
	return
}

func createIndiClieBase(date *time.Time) (err error) {
	tName := fmt.Sprintf("TMP_INDI_CLIE_BASE_%s", date.Format("20060102"))
	createSql := fmt.Sprintf(`CREATE TABLE IF NOT EXISTS %s
	(CUID UInt64,EMID UInt32,SHKHS Float64,MSKKHS Float64,BJKKHS Float64,JKKHS Float64,YJXKHS Float64,PTKHS Float64,BXHS Float64,
	 ZJLBL Float64,CXBL Float64,LCBL Float64,GZBL Float64,JJBL Float64,BXBL Float64,SFCGBL Float64,SFDXBL Float64)
	ENGINE=Memory`, tName)
	if ok, err := createTable(tName, createSql); err != nil || !ok {
		return err
	}
	dateStr := date.Format("2006-01-02")
	CreateIndiYgfc(dateStr)
	sqlStr := fmt.Sprintf(`INSERT INTO %s 
	WITH toDate(?) AS YEDATE,toStartOfYear(YEDATE) AS YSDATE,
		if(SDATE<YSDATE,YSDATE,SDATE) AS SDATE2,if(EDATE>YEDATE,YEDATE,EDATE) AS EDATE2,
		dateDiff('day',YSDATE,YEDATE)+1 AS DAYS1,dateDiff('day',SDATE2,EDATE2)+1 AS DAYS2,
		dictGetUInt32('DICT_USER_PUBLIC','EMID',toUInt64(UTID)) AS PEMID
	select CUID,EMID,if(EMID2=0,PEMID,EMID2) EMID,SHKHS*DSVL,MSKKHS*DSVL,BJKKHS*DSVL,JKKHS*DSVL,YJXKHS*DSVL,PTKHS*DSVL,BXHS*DSVL,
	    ZJLBL*DSVL,CXBL*DSVL,LCBL*DSVL,GZBL*DSVL,JJBL*DSVL,BXBL*DSVL,SFCGBL*DSVL,SFDXBL*DSVL
	from T_INDI_CLIE_ACBL_BASE a left join (SELECT CUID,DIVI.EMID EMID,DIVI.DSVL DSVL FROM TMP_INDI_CLIE_DIVI AYYAY JOIN DIVI) b on a.CUID=b.CUID
	where a.BSNF=toYear(YEDATE)`, tName)
	err = base.CHDB.Exec(sqlStr, dateStr).Error
	return
}

//CreateIndiYgfc 构造零售客户认定关系
func CreateIndiYgfc(currDate string) (err error) {
	strSql := "CREATE TABLE IF NOT EXISTS TMP_INDI_CLIE_DIVI(CUID UInt64,`DIVI.DPID` Array(UInt32),`DIVI.EMID` Array(UInt32),`DIVI.DSVL` Array(Float64)) ENGINE = Join(ANY,LEFT,CUID)"
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("TRUNCATE TABLE TMP_INDI_CLIE_DIVI").Error
	if err != nil {
		return
	}
	strSql = "CREATE TABLE IF NOT EXISTS TMP_INDI_CLIE_DIVI2(CUID UInt64,`DIVI.DPID` Array(UInt32),`DIVI.EMID` Array(UInt32),`DIVI.DSVL` Array(Float64)) ENGINE = Join(ANY,LEFT,CUID)"
	err = base.CHDB.Exec(strSql).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("TRUNCATE TABLE TMP_INDI_CLIE_DIVI2").Error
	if err != nil {
		return
	}
	strSql = `INSERT INTO TMP_INDI_CLIE_DIVI(CUID,DIVI.DPID,DIVI.EMID,DIVI.DSVL)
	SELECT A.CUID,groupArray(B.DPID) DPIDS,groupArray(B.EMID) EMIDS,groupArray(B.ACBL/A.ACBL) DSVLS FROM
	(SELECT CUID,SUM(ACBL) ACBL FROM
	(SELECT CUID,SUM(ACBL1) ACBL FROM DF_INDI_ACCO_ACBL WHERE DTID=toDate(?) AND ACBL1>0 GROUP BY CUID
	UNION ALL
	SELECT CUID,SUM(ACBL1) ACBL FROM DF_INDI_FANA_ACBL WHERE DTID=toDate(?) AND ACBL1>0 GROUP BY CUID)
	GROUP BY CUID) A
	INNER JOIN
	(SELECT CUID,DPID,EMID,SUM(ACBL) ACBL FROM
	(SELECT CUID,DPID,EMID,SUM(ACBL1) ACBL FROM DF_INDI_ACCO_ACBL WHERE DTID=toDate(?) AND ACBL1>0 GROUP BY CUID,DPID,EMID
	UNION ALL
	SELECT CUID,DPID,EMID,SUM(ACBL1) ACBL FROM DF_INDI_FANA_ACBL WHERE DTID=toDate(?) AND ACBL1>0 GROUP BY CUID,DPID,EMID)
	GROUP BY CUID,DPID,EMID
	) B ON A.CUID=B.CUID
	GROUP BY A.CUID`
	err = base.CHDB.Exec(strSql, currDate, currDate, currDate, currDate).Error
	if err != nil {
		return
	}
	strSql = `INSERT INTO TMP_INDI_CLIE_DIVI2(CUID,DIVI.DPID,DIVI.EMID,DIVI.DSVL)
	SELECT B.CUID,DIVI.DPID DPIDS,DIVI.EMID EMIDS,DIVI.DSVL DSVLS
	FROM T_INDI_ACCO_DIVI_ZIPPER A INNER JOIN
	(SELECT CUID,MAX(ID) ACID FROM T_INDI_ACCO WHERE CUID NOT IN (SELECT CUID FROM TMP_INDI_CLIE_DIVI) GROUP BY CUID) B ON A.ACID=B.ACID
	WHERE toDate(?)>=A.SDATE AND toDate(?)<=A.EDATE`
	err = base.CHDB.Exec(strSql, currDate, currDate).Error
	if err != nil {
		return
	}
	err = base.CHDB.Exec("INSERT INTO TMP_INDI_CLIE_DIVI SELECT * FROM TMP_INDI_CLIE_DIVI2").Error
	return
}
